package iyogo;

import java.util.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;

public class ManipulateSQL {
	public void addMember(MEMBER mem) {
		Connection conn = DBUtil.createConn();
		String sql = "insert into Membership(MEMBER_ID, MEMBER_NAME, ADDRESS, PROFESSION) values(null, ?,?,?)";
		PreparedStatement ps = DBUtil.prepare(conn, sql);
		try {
			ps.setString(1, mem.getName());
			ps.setString(2, mem.getAddress());
			ps.setString(3, mem.getProfession());
			ps.executeUpdate();
		} catch(SQLException e) {
			e.printStackTrace();
		}
		DBUtil.close(ps);
		DBUtil.close(conn);
	}
	
	public MEMBER SearchByName(String MemName) {
		Connection conn = DBUtil.createConn();
		String sql = "select * from Membership where MEMBER_NAME = ?";
		PreparedStatement ps = DBUtil.prepare(conn, sql);
		MEMBER mem = null;
		ResultSet rs = null;
		try {
			ps.setString(1, MemName);
			rs = ps.executeQuery();
			if (rs.next()) {
				mem = new MEMBER();
				mem.setID(rs.getInt("MEMBER_ID"));
				mem.setName(rs.getString("MEMBER_NAME"));
				mem.setAddress(rs.getString("ADDRESS"));
				mem.setProfession(rs.getString("PROFESSION"));
			}
		} catch(SQLException e) {
			e.printStackTrace();
		}
		DBUtil.close(rs);
		DBUtil.close(ps);
		DBUtil.close(conn);
		return mem;
	}
	
	/* addCard
	 * CARD_ID
	 * NUM
	 * ACTIVE_DATE
	*/
	public void addCard(String memName, String category, int Num) {
		int CardID = 0;
		int MemID = 0;
		Connection conn = DBUtil.createConn();
		String sql = "insert into CardInfo(CARD_ID, NUM, ACTIVE_DATE) values(null, ?,?)";
		PreparedStatement stm = null;
		try {
			stm = (PreparedStatement)conn.prepareStatement(
					sql, Statement.RETURN_GENERATED_KEYS);
			stm.setInt(1, -1);
			stm.setInt(2, 365);
			stm.executeUpdate();
			conn.commit();
			ResultSet resultSet = stm.getGeneratedKeys();
			while (resultSet.next())
				CardID = resultSet.getInt(1);
			sql = "select MEMBER_ID from MEMBER where MEMBER_NAME = ?";
			stm = (PreparedStatement)conn.prepareStatement(sql);
			stm.setString(1, memName);
			stm.executeQuery();
			conn.commit();
			resultSet = stm.getResultSet();
			resultSet.next();
			MemID = resultSet.getInt(1);
			Date CurrentDate = new Date(System.currentTimeMillis());
			Date ExpireDate = CurrentDate;
			ExpireDate.setYear(ExpireDate.getYear() + 1);
			addMapping(CardID, MemID, CurrentDate, ExpireDate, category, Num);
			
		} catch(SQLException e) {
			e.printStackTrace();
		}
		DBUtil.close(stm);
		DBUtil.close(conn);		
	}
	
	
	/*
	 *RELATION_ID
	 *MEMBER_ID
	 *CARD_ID
	 *ACTIVE_DATE
	 *EXPIRE_DATE
	 *Num
	 *CardType
	 */
	public void addMapping(int CardID, int MemID, Date CurrentDate, Date ExpireDate, String CardType, int Num) {
		PreparedStatement stm = null;
		Connection conn = DBUtil.createConn();
		String sql = "insert into Mapping(RELATION_ID, MEMBER_ID, CARD_ID, ACTIVE_DATE, EXPIRE_DATE, Num, CardType)values(null,?,?,?,?,?)";
		try {
			stm = (PreparedStatement)conn.prepareStatement(sql);
			stm.setInt(1, MemID);
			stm.setInt(2, CardID);
			stm.setDate(3, new java.sql.Date(CurrentDate.getTime()));
			stm.setDate(4, new java.sql.Date(ExpireDate.getTime()));
			stm.setString(5, CardType);
			stm.setInt(6, Num);
			stm.executeUpdate();
			conn.commit();
		} catch(SQLException e) {
			e.printStackTrace();
		}
		DBUtil.close(stm);
		DBUtil.close(conn);
	}
}


















